--创建为触发器执行的函数:
CREATE OR REPLACE FUNCTION sc_user_student_func() RETURNS TRIGGER AS $sc_user_student_audit$
    BEGIN
        -- 在sc_user_student_audit中创建一行以反映在emp上执行的操作,
        -- 使用特殊的变量TG_OP来计算这个操作.
				--sc_user_student表执行删除操作时候,把删除的这条数据从sc_user表删除(OLD.*表示旧数据,NEW.*表示新数据)
        IF (TG_OP = 'DELETE') THEN
						delete from baseinfo.sc_user where id = OLD.fk_user;
            RETURN OLD;
				--sc_user_student表执行修改操作时候,把修改的这条新数据放到sc_user表
        ELSIF (TG_OP = 'UPDATE') THEN
						update baseinfo.sc_user
							set
								status = NEW.status,
								update_by = NEW.update_by,
								update_time = NEW.update_time,
								id_number = NEW.sfzjh,
								phone = NEW.lxdh,
								email = NEW.email,
								name = NEW.name,
								fk_unit = NEW.fk_unit
							where id = OLD.fk_user;
							RETURN NEW;
				--sc_user_student表执行插入操作时候,把插入的的这条新数据放到sc_user表
        ELSIF (TG_OP = 'INSERT') THEN
						insert into baseinfo.sc_user
						(
							id,type,create_by,create_time,status,name,username,id_number,phone,fk_unit,email
						)
						values
						(
							NEW.id,'2',NEW.create_by,NEW.create_time,NEW.status,NEW.name,NEW.yhm,NEW.sfzjh,NEW.lxdh,NEW.fk_unit,NEW.email
						);
						RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$sc_user_student_audit$ LANGUAGE plpgsql;

--为表sc_user添加触发器,然后执行的函数
drop trigger sc_user_student_audit on baseinfo.sc_user_student;
CREATE TRIGGER sc_user_student_audit
AFTER INSERT OR UPDATE OR DELETE ON baseinfo.sc_user_student --在sc_user表每一行操作之后,执行增删查改时触发
FOR EACH ROW EXECUTE PROCEDURE sc_user_student_func();--执行的函数



--创建为触发器执行的函数:
CREATE OR REPLACE FUNCTION sc_user_teacher_func() RETURNS TRIGGER AS $sc_user_teacher_audit$
    BEGIN
        -- 在sc_user_teacher_audit中创建一行以反映在emp上执行的操作,
        -- 使用特殊的变量TG_OP来计算这个操作.
				--sc_user_teacher表执行删除操作时候,把删除的这条数据从sc_user表删除(OLD.*表示旧数据,NEW.*表示新数据)
        IF (TG_OP = 'DELETE') THEN
						delete from baseinfo.sc_user where id = OLD.fk_user;
            RETURN OLD;
				--sc_user_teacher表执行修改操作时候,把修改的这条新数据放到sc_user表
        ELSIF (TG_OP = 'UPDATE') THEN
						update baseinfo.sc_user
							set
								status = NEW.status,
								update_by = NEW.update_by,
								update_time = NEW.update_time,
								id_number = NEW.sfzjh,
								phone = NEW.phone,
								email = NEW.email,
								name = NEW.name,
								fk_unit = NEW.gzdwpk
							where id = OLD.fk_user;
							RETURN NEW;
				--sc_user_teacher表执行插入操作时候,把插入的的这条新数据放到sc_user表
        ELSIF (TG_OP = 'INSERT') THEN
						insert into baseinfo.sc_user
						(
							id,type,create_by,create_time,status,name,username,id_number,phone,fk_unit,email
						)
						values
						(
							NEW.id,'1',NEW.create_by,NEW.create_time,NEW.status,NEW.name,NEW.yhm,NEW.sfzjh,NEW.phone,NEW.gzdwpk,NEW.email
						);
						RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$sc_user_teacher_audit$ LANGUAGE plpgsql;

--为表sc_user添加触发器,然后执行的函数
drop trigger sc_user_teacher_audit on baseinfo.sc_user_teacher;
CREATE TRIGGER sc_user_teacher_audit
AFTER INSERT OR UPDATE OR DELETE ON baseinfo.sc_user_teacher --在sc_user表每一行操作之后,执行增删查改时触发
FOR EACH ROW EXECUTE PROCEDURE sc_user_teacher_func();--执行的函数
